package sixyz.dao;

import sixyz.entity.Book;
import sixyz.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;

public class BookDAO {

    private final JDBCUtils jdbcUtils = new JDBCUtils();
    private final BaseDAO<Book> baseDAO = new BaseDAO<>();

    // 单实例
    private static final BookDAO bookDAO = new BookDAO();
    private BookDAO() {}
    public static BookDAO getInstance() {
        return bookDAO;
    }

    // query all books
    public ArrayList<Book> queryAllBooks() throws SQLException, ClassNotFoundException {
        Connection connection = jdbcUtils.getConnection();
        String sqlStatement_selectAll = "SELECT * FROM book";
        ArrayList<Book> bookData = new ArrayList<>();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sqlStatement_selectAll);
        while (resultSet.next()) {
            Integer id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String author = resultSet.getString("author");
            String description = resultSet.getString("description");
            bookData.add(new Book(id, name, author, description));
        }
        jdbcUtils.release(resultSet, statement, connection);
        return  bookData;
    }

    // insert a book
    public void insertABook(Book book) throws SQLException, ClassNotFoundException, IllegalAccessException {
        baseDAO.save(book);
    }

    // query one book
    public Book queryBookById(Integer id) throws SQLException, ClassNotFoundException {
        Connection connection = jdbcUtils.getConnection();
        String sqlStatement_selectSpecific = "SELECT * FROM book WHERE id = ?";
        Book book = null;
        PreparedStatement preparedStatement = connection.prepareStatement(sqlStatement_selectSpecific);
        preparedStatement.setInt(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            String name = resultSet.getString("name");
            String author = resultSet.getString("author");
            String description = resultSet.getString("description");
            book = new Book(id, name, author, description);
        }
        jdbcUtils.release(resultSet, preparedStatement, connection);
        return book;
    }

    // delete one book
    public void deleteBookById(Integer id) throws SQLException, ClassNotFoundException {
        Connection connection = jdbcUtils.getConnection();
        String sqlStatement_delete = "DELETE FROM book WHERE id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sqlStatement_delete);
        preparedStatement.setInt(1, id);
        preparedStatement.executeUpdate();
        jdbcUtils.release(preparedStatement, connection);
    }

}
